package zy.dao.batch.client.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.batch.client.ClientDAO;
import zy.dto.batch.money.ClientBackAnalysisDto;
import zy.dto.batch.money.ClientMoneyDetailsDto;
import zy.entity.batch.client.T_Batch_Client;
import zy.entity.batch.client.T_Batch_Client_Shop;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class ClientDAOImpl extends BaseDaoImpl implements ClientDAO{
	
	@Override
	public Integer count(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_batch_client t");
		sql.append(" JOIN t_batch_client_info ci ON t.ci_code = ci.ci_code AND t.companyid = ci.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(t.ci_code,:searchContent)>0 OR INSTR(ci_name,:searchContent)>0 OR INSTR(ci_spell,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}
	
	@Override
	public List<T_Batch_Client> list(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ci_id,t.ci_code,t.companyid,ci_name,ci_spell,ci_area,ci_rate,ci_init_debt,ci_receivable,ci_received,ci_prepay,ci_default,ci_sp_code,");
		sql.append(" ci_lastdate,ci_batch_cycle,ci_settle_cycle,ci_addr,ci_tel,ci_man,ci_mobile,ci_bank_open,ci_bank_code,ci_remark,ci_earnest,");
		sql.append(" ci_deposit,ci_use_credit,ci_credit_limit");
		sql.append(" FROM t_batch_client t");
		sql.append(" JOIN t_batch_client_info ci ON t.ci_code = ci.ci_code AND t.companyid = ci.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(t.ci_code,:searchContent)>0 OR INSTR(ci_name,:searchContent)>0 OR INSTR(ci_spell,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY ci_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Batch_Client.class));
	}
	
	@Override
	public List<T_Batch_Client> list4dialog(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ci_id,t.ci_code,t.companyid,ci_name,ci_rate,ci_default,ci_use_credit,ci_credit_limit,ci_tel,ci_man,ci_mobile,");
		sql.append(" IFNULL(ci_receivable,0) AS ci_receivable,IFNULL(ci_received,0) AS ci_received,IFNULL(ci_prepay,0) AS ci_prepay");
		sql.append(" FROM t_batch_client t");
		sql.append(" JOIN t_batch_client_info ci ON t.ci_code = ci.ci_code AND t.companyid = ci.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(t.ci_code,:searchContent)>0 OR INSTR(ci_name,:searchContent)>0 OR INSTR(ci_spell,:searchContent)>0)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY ci_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Batch_Client.class));
	}
	
	@Override
	public T_Batch_Client load(Integer ci_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ci_id,t.ci_code,t.companyid,ci_name,ci_spell,ci_area,ci_rate,ci_init_debt,ci_receivable,ci_received,ci_prepay,ci_default,ci_sp_code,");
		sql.append(" ci_lastdate,ci_batch_cycle,ci_settle_cycle,ci_addr,ci_tel,ci_man,ci_mobile,ci_bank_open,ci_bank_code,ci_remark,ci_earnest,");
		sql.append(" ci_deposit,ci_use_credit,ci_credit_limit,");
		sql.append(" (SELECT ar_name FROM t_base_area ar WHERE ar_code = ci_area AND ar.companyid = t.companyid LIMIT 1) AS ar_name");
		sql.append(" FROM t_batch_client t");
		sql.append(" JOIN t_batch_client_info ci ON t.ci_code = ci.ci_code AND t.companyid = ci.companyid");
		sql.append(" WHERE ci_id = :ci_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ci_id", ci_id),
					new BeanPropertyRowMapper<>(T_Batch_Client.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Batch_Client load(String ci_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ci_id,t.ci_code,t.companyid,ci_name,ci_spell,ci_area,ci_rate,ci_init_debt,ci_receivable,ci_received,ci_prepay,ci_default,ci_sp_code,");
		sql.append(" ci_lastdate,ci_batch_cycle,ci_settle_cycle,ci_addr,ci_tel,ci_man,ci_mobile,ci_bank_open,ci_bank_code,ci_remark,ci_earnest,");
		sql.append(" ci_deposit,ci_use_credit,ci_credit_limit,");
		sql.append(" (SELECT ar_name FROM t_base_area ar WHERE ar_code = ci_area AND ar.companyid = t.companyid LIMIT 1) AS ar_name");
		sql.append(" FROM t_batch_client t");
		sql.append(" JOIN t_batch_client_info ci ON t.ci_code = ci.ci_code AND t.companyid = ci.companyid");
		sql.append(" WHERE t.ci_code = :ci_code AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ci_code", ci_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Batch_Client.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Batch_Client check(T_Batch_Client client) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ci_id,t.ci_code,t.companyid,ci_name");
		sql.append(" FROM t_batch_client t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND ci_sp_code = :ci_sp_code ");//上级店铺编号
		sql.append(" AND ci_name = :ci_name");
		sql.append(" AND t.companyid = :companyid");
		if(client.getCi_id() != null && client.getCi_id()>0 ){
			sql.append(" AND ci_id <> :ci_id");
		}
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),new BeanPropertySqlParameterSource(client),
					new BeanPropertyRowMapper<>(T_Batch_Client.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Batch_Client_Shop check_client_shop(T_Batch_Client_Shop client_shop) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT cis_id,t.cis_code,cis_ci_code,cis_name,t.companyid");
		sql.append(" FROM t_batch_client_shop t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND cis_ci_code = :cis_ci_code ");
		sql.append(" AND cis_name = :cis_name");
		if(client_shop.getCis_code() != null || !"".equals(client_shop.getCis_code())){
			sql.append(" AND cis_code != :cis_code");
		}
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),new BeanPropertySqlParameterSource(client_shop),
					new BeanPropertyRowMapper<>(T_Batch_Client_Shop.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(T_Batch_Client client) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(ci_code+0)) from t_batch_client ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(client), String.class);
		client.setCi_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_batch_client");
		sql.append(" (ci_code,ci_name,ci_spell,ci_area,ci_rate,ci_init_debt,ci_receivable,ci_received,ci_prepay,ci_default,");
		sql.append(" ci_sp_code,ci_lastdate,ci_batch_cycle,ci_settle_cycle,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ci_code,:ci_name,:ci_spell,:ci_area,:ci_rate,:ci_init_debt,:ci_receivable,:ci_received,:ci_prepay,:ci_default,");
		sql.append(" :ci_sp_code,:ci_lastdate,:ci_batch_cycle,:ci_settle_cycle,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client),holder);
		client.setCi_id(holder.getKey().intValue());
		sql.setLength(0);
		sql.append("INSERT INTO t_batch_client_info");
		sql.append(" (ci_code,ci_addr,ci_tel,ci_man,ci_mobile,ci_bank_open,ci_bank_code,ci_remark,ci_earnest,ci_deposit,ci_use_credit,ci_credit_limit,companyid)");
		sql.append(" VALUES(:ci_code,:ci_addr,:ci_tel,:ci_man,:ci_mobile,:ci_bank_open,:ci_bank_code,:ci_remark,:ci_earnest,:ci_deposit,:ci_use_credit,:ci_credit_limit,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client));
	}
	
	@Override
	public void save_client_shop(T_Batch_Client_Shop client_shop) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(cis_code+0)) from t_batch_client_shop ");
		sql.append(" WHERE 1=1");
		sql.append(" AND cis_ci_code=:cis_ci_code");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(client_shop), String.class);
		client_shop.setCis_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_batch_client_shop");
		sql.append(" (cis_code,cis_ci_code,cis_name,cis_spell,cis_linkman,cis_link_tel,cis_link_mobile,cis_link_adr,cis_stutas,companyid)");
		sql.append(" VALUES");
		sql.append(" (:cis_code,:cis_ci_code,:cis_name,:cis_spell,:cis_linkman,:cis_link_tel,:cis_link_mobile,:cis_link_adr,:cis_stutas,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client_shop),holder);
		client_shop.setCis_id(holder.getKey().intValue());
	}

	@Override
	public void update(T_Batch_Client client) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_client");
		sql.append(" SET ci_name=:ci_name");
		sql.append(" ,ci_spell=:ci_spell");
		sql.append(" ,ci_area=:ci_area");
		sql.append(" ,ci_rate=:ci_rate");
		if(client.getCi_init_debt() != null){
			sql.append(" ,ci_init_debt=:ci_init_debt");
		}
		sql.append(" ,ci_default=:ci_default");
		sql.append(" ,ci_batch_cycle=:ci_batch_cycle");
		sql.append(" ,ci_settle_cycle=:ci_settle_cycle");
		sql.append(" WHERE ci_id=:ci_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client));
		sql.setLength(0);
		sql.append("UPDATE t_batch_client_info");
		sql.append(" SET ci_addr=:ci_addr");
		sql.append(" ,ci_tel=:ci_tel");
		sql.append(" ,ci_man=:ci_man");
		sql.append(" ,ci_mobile=:ci_mobile");
		sql.append(" ,ci_bank_open=:ci_bank_open");
		sql.append(" ,ci_bank_code=:ci_bank_code");
		sql.append(" ,ci_remark=:ci_remark");
		sql.append(" ,ci_earnest=:ci_earnest");
		sql.append(" ,ci_deposit=:ci_deposit");
		sql.append(" ,ci_use_credit=:ci_use_credit");
		sql.append(" ,ci_credit_limit=:ci_credit_limit");
		sql.append(" WHERE ci_code=:ci_code AND companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client));
	}
	
	@Override
	public void del(Integer ci_id, String ci_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_batch_client");
		sql.append(" WHERE ci_id=:ci_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ci_id", ci_id));
		sql.setLength(0);
		sql.append(" DELETE FROM t_batch_client_info");
		sql.append(" WHERE ci_code=:ci_code");
		sql.append(" AND companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ci_code", ci_code).addValue("companyid", companyid));
	}

	@Override
	public T_Batch_Client loadClient(String ci_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ci_id,ci_code,");
		sql.append(" IFNULL(ci_receivable,0) AS ci_receivable,IFNULL(ci_received,0) AS ci_received,IFNULL(ci_prepay,0) AS ci_prepay");
		sql.append(" FROM t_batch_client t");
		sql.append(" WHERE ci_code = :ci_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ci_code", ci_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Batch_Client.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void updateReceivable(T_Batch_Client client) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_client SET ci_receivable=:ci_receivable WHERE ci_id=:ci_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client));
	}
	
	@Override
	public void updatePrepay(T_Batch_Client client) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_client SET ci_prepay=:ci_prepay WHERE ci_id=:ci_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client));
	}
	
	@Override
	public void updateSettle(T_Batch_Client client) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_client ");
		sql.append(" SET ci_receivable=:ci_receivable ");
		sql.append(" ,ci_received=:ci_received");
		sql.append(" ,ci_prepay=:ci_prepay");
		sql.append(" WHERE ci_id=:ci_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client));
	}

	private String getMoneyDetailsSQL(Map<String, Object> params,String type){
		if (StringUtil.isNotEmpty(params.get("type"))) {
			params.put("types", Arrays.asList(params.get("type").toString().split(",")));
		}
		if (StringUtil.isNotEmpty(params.get("pay_state"))) {
			params.put("pay_states", Arrays.asList(params.get("pay_state").toString().split(",")));
		}
		StringBuffer sql = new StringBuffer();
		if("sell".equals(type)){
			if(StringUtil.isNotEmpty(params.get("client_code"))){
				sql.append(" AND se_client_code = :client_code ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND se_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND se_make_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND se_make_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(se_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				sql.append(" AND se_type IN (:types) ");
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				sql.append(" AND se_pay_state IN (:pay_states) ");
			}
			sql.append(" AND se_ar_state = 1");
		}else if("fee".equals(type)){
			if(StringUtil.isNotEmpty(params.get("client_code"))){
				sql.append(" AND fe_client_code = :client_code ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND fe_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND fe_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND fe_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(fe_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				sql.append(" AND 3 IN (:types) ");
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				sql.append(" AND fe_pay_state IN (:pay_states) ");
			}
			sql.append(" AND fe_ar_state = 1");
		}else if("prepay".equals(type)){
			if(StringUtil.isNotEmpty(params.get("client_code"))){
				sql.append(" AND pp_client_code = :client_code ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND pp_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND pp_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND pp_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(pp_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				String _type = StringUtil.trimString(params.get("type"));
				if(_type.contains("4") && _type.contains("5")){
				}else if(_type.contains("4")){
					sql.append(" AND pp_money > 0");
				}else if(_type.contains("5")){
					sql.append(" AND pp_money < 0");
				}else{
	        		sql.append(" AND 1=2 ");
	        	}
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				String _pay_state = StringUtil.trimString(params.get("pay_state"));
				if(!_pay_state.contains("2")){
					sql.append(" AND 1=2 ");
				}
			}
			sql.append(" AND pp_ar_state = 1");
		}
		sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Map<String, Object> countsumMoneyDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" COUNT(1) AS totalCount,");
		sql.append(" SUM(money) AS money,");
		sql.append(" SUM(discount_money) AS discount_money,");
		sql.append(" SUM(prepay) AS prepay,");
		sql.append(" SUM(received) AS received,");
		sql.append(" SUM(receivable) AS receivable");
		sql.append(" FROM (");
		sql.append(" SELECT se_money AS money,se_discount_money AS discount_money,");
		sql.append(" se_prepay AS prepay,se_received AS received,se_receivable AS receivable");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "sell"));
		sql.append(" UNION ALL");
		sql.append(" SELECT fe_money AS money,fe_discount_money AS discount_money,");
		sql.append(" fe_prepay AS prepay,fe_received AS received,fe_receivable AS receivable");
		sql.append(" FROM t_batch_fee t");
		sql.append(" JOIN t_batch_client ci ON ci_code = fe_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getMoneyDetailsSQL(params, "fee"));
		sql.append(" UNION ALL ");
		sql.append(" SELECT 0 AS money,0 AS discount_money,0 AS prepay,pp_money AS received,0 AS receivable");
		sql.append(" FROM t_batch_prepay t");
		sql.append(" JOIN t_batch_client ci ON ci_code = pp_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getMoneyDetailsSQL(params, "prepay"));
		sql.append(" )temp");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<ClientMoneyDetailsDto> listMoneyDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM (");
		sql.append(" SELECT se_id AS id,se_make_date AS make_date,se_number AS number,se_type AS type,se_client_code AS client_code,");
		sql.append(" se_pay_state AS pay_state,se_money AS money,se_discount_money AS discount_money,");
		sql.append(" se_prepay AS prepay,se_received AS received,se_receivable AS receivable,se_manager AS manager,");
		sql.append(" ci_name AS client_name");
		sql.append(" FROM t_batch_sell t");
		sql.append(" JOIN t_batch_client ci ON ci_code = se_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "sell"));
		sql.append(" UNION ALL");
		sql.append(" SELECT fe_id AS id,fe_date AS make_date,fe_number AS number,3 AS type,fe_client_code AS client_code,");
		sql.append(" fe_pay_state AS pay_state,fe_money AS money,fe_discount_money AS discount_money,");
		sql.append(" fe_prepay AS prepay,fe_received AS received,fe_receivable AS receivable,fe_manager AS manager,");
		sql.append(" ci_name AS client_name");
		sql.append(" FROM t_batch_fee t");
		sql.append(" JOIN t_batch_client ci ON ci_code = fe_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getMoneyDetailsSQL(params, "fee"));
		sql.append(" UNION ALL ");
		sql.append(" SELECT pp_id AS id,pp_date AS make_date,pp_number AS number,IF (pp_money > 0, 4, 5) AS type,pp_client_code AS client_code,");
		sql.append(" 2 AS pay_state,0 AS money,0 AS discount_money,0 AS prepay,pp_money AS received,0 AS receivable,pp_manager AS manager,");
		sql.append(" ci_name AS client_name");
		sql.append(" FROM t_batch_prepay t");
		sql.append(" JOIN t_batch_client ci ON ci_code = pp_client_code AND ci.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getMoneyDetailsSQL(params, "prepay"));
		sql.append(" )temp ");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else{
			sql.append(" ORDER BY make_date DESC ");
		}
		sql.append(" LIMIT :start,:end");
		
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ClientMoneyDetailsDto.class));
	}
	
	@Override
	public List<ClientBackAnalysisDto> listBackAnalysis(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ci_id,t.ci_code,ci_name,ci_credit_limit,t.companyid,");
		sql.append(" IFNULL((SELECT SUM(pp_money) FROM t_batch_prepay pp ");
		sql.append(" WHERE pp_client_code = t.ci_code AND pp.companyid = t.companyid");
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND pp_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND pp_date <= :enddate ");
		}
		sql.append(" ),0) AS ci_prepay,");
		sql.append(" (SELECT MAX(se_make_date) FROM t_batch_sell se WHERE se_client_code = t.ci_code AND se.companyid = t.companyid AND se_type = 0) AS ci_lastdate");
		sql.append(" FROM t_batch_client t");
		sql.append(" JOIN t_batch_client_info ci ON t.ci_code = ci.ci_code AND t.companyid = ci.companyid");
		sql.append(" WHERE 1 = 1");
		if(StringUtil.isNotEmpty(params.get("ci_code"))){
			sql.append(" AND t.ci_code = :ci_code");
		}
		sql.append(" AND ci_sp_code = :shop_code ");//上级店铺编号
		sql.append(" AND t.companyid=:companyid");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else{
			sql.append(" ORDER BY ci_id DESC ");
		}
		List<ClientBackAnalysisDto> results = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ClientBackAnalysisDto.class));
		if (results == null || results.size() == 0) {
			return results;
		}
		for (ClientBackAnalysisDto dto : results) {
			if(StringUtil.isNotEmpty(dto.getCi_lastdate())){
				dto.setNosenddays(DateUtil.getDaysBetween(dto.getCi_lastdate(), DateUtil.getYearMonthDate())+"");
			}
		}
		sql.setLength(0);
		sql.append(" SELECT ci_code,se_type AS type,");
		sql.append(" SUM(se_money-se_rebatemoney+se_stream_money) AS money,");
		sql.append(" SUM(se_prepay) AS prepay,");
		sql.append(" SUM(se_discount_money) AS discount_money,");
		sql.append(" SUM(se_receivable) AS receivable,");
		sql.append(" SUM(se_received) AS received");
		sql.append(" FROM t_batch_client t");
		sql.append(" JOIN t_batch_sell se ON se_client_code = ci_code AND se.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND se_ar_state = 1");
		if(StringUtil.isNotEmpty(params.get("ci_code"))){
			sql.append(" AND t.ci_code = :ci_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND se_make_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND se_make_date <= :enddate ");
		}
		sql.append(" AND ci_sp_code = :shop_code ");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY ci_code,se_type");
		sql.append(" UNION");
		sql.append(" SELECT ci_code,3 AS type,");
		sql.append(" SUM(fe_money) AS money,");
		sql.append(" SUM(fe_prepay) AS prepay,");
		sql.append(" SUM(fe_discount_money) AS discount_money,");
		sql.append(" SUM(fe_receivable) AS receivable,");
		sql.append(" SUM(fe_received) AS received");
		sql.append(" FROM t_batch_client t");
		sql.append(" JOIN t_batch_fee fe ON fe_client_code = ci_code AND fe.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND fe_ar_state = 1");
		if(StringUtil.isNotEmpty(params.get("ci_code"))){
			sql.append(" AND t.ci_code = :ci_code");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND fe_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND fe_date <= :enddate ");
		}
		sql.append(" AND ci_sp_code = :shop_code ");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY ci_code");
		List<Map<String, Object>> list = namedParameterJdbcTemplate.queryForList(sql.toString(), params);
		for(Map<String, Object> item:list){
			String ci_code = StringUtil.trimString(item.get("ci_code"));
			String type = StringUtil.trimString(item.get("type"));
			for (ClientBackAnalysisDto dto : results) {
				if(ci_code.equals(dto.getCi_code())){
					dto.setDiscount_money(dto.getDiscount_money()+Double.parseDouble(item.get("discount_money").toString()));
					dto.setPrepay(dto.getPrepay()+Double.parseDouble(item.get("prepay").toString()));
					dto.setReceivable(dto.getReceivable()+Double.parseDouble(item.get("receivable").toString()));
					dto.setReceived(dto.getReceived()+Double.parseDouble(item.get("received").toString()));
					if("0".equals(type)){
						dto.setSell_money(Double.parseDouble(item.get("money").toString()));
					}else if("1".equals(type)){
						dto.setReturn_money(Double.parseDouble(item.get("money").toString()));
					}else if("2".equals(type)){
						dto.setInit_money(Double.parseDouble(item.get("money").toString()));
					}else if("3".equals(type)){
						dto.setFee_money(Double.parseDouble(item.get("money").toString()));
					}
					break;
				}
			}
		}
		return results;
	}

	@Override
	public List<T_Batch_Client_Shop> shop_list(Map<String, Object> params) {
		Object searchContent = params.get("searchContent");
		Object cis_stutas = params.get("cis_stutas");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cis_id,cis_code,cis_ci_code,cis_name,cis_spell,cis_linkman,cis_link_tel,cis_link_mobile,cis_link_adr,cis_stutas,t.companyid");
		sql.append(" FROM t_batch_client_shop t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND cis_ci_code = :ci_code ");
		if(StringUtil.isNotEmpty(searchContent)){
        	sql.append(" AND (INSTR(t.cis_code,:searchContent)>0 OR INSTR(cis_name,:searchContent)>0 OR INSTR(cis_spell,:searchContent)>0)");
        }
		if(StringUtil.isNotEmpty(cis_stutas)){
			sql.append(" AND cis_stutas = :cis_stutas");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY cis_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Batch_Client_Shop.class));
	}

	@Override
	public void del_client_shop(Map<String, Object> params) {
		Object cis_id = params.get("cis_id");
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_batch_client_shop");
		sql.append(" WHERE cis_id=:cis_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("cis_id", cis_id));
	}

	@Override
	public T_Batch_Client_Shop load_shop(Integer cis_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT cis_id,cis_code,cis_ci_code,cis_name,cis_spell,cis_linkman,cis_link_tel,cis_link_mobile,cis_link_adr,cis_stutas,t.companyid");
		sql.append(" FROM t_batch_client_shop t");
		sql.append(" WHERE cis_id = :cis_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("cis_id", cis_id),
					new BeanPropertyRowMapper<>(T_Batch_Client_Shop.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void update_client_shop(T_Batch_Client_Shop client_Shop) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_batch_client_shop");
		sql.append(" SET cis_name=:cis_name");
		sql.append(" ,cis_spell=:cis_spell");
		sql.append(" ,cis_linkman=:cis_linkman");
		sql.append(" ,cis_link_tel=:cis_link_tel");
		sql.append(" ,cis_link_mobile=:cis_link_mobile");
		sql.append(" ,cis_link_adr=:cis_link_adr");
		sql.append(" ,cis_stutas=:cis_stutas");
		sql.append(" WHERE cis_id=:cis_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(client_Shop));
	}
}
